﻿using EOM.TSHotelManagement.Common.Core;
using EOM.TSHotelManagement.Migration;
using EOM.TSHotelManagement.Shared;
using EOM.TSHotelManagement.WebApi.Constant;
using Microsoft.AspNetCore.Builder;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using MySqlConnector;
using Npgsql;
using Oracle.ManagedDataAccess.Client;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;

namespace EOM.TSHotelManagement.WebApi
{
    public class InitializeConfig
    {
        private readonly IConfiguration config;

        public InitializeConfig(IConfiguration configuration)
        {
            config = configuration;
        }

        #region initlize database
        public void InitializeDatabase(IApplicationBuilder app)
        {
            using var scope = app.ApplicationServices.CreateScope();
            var logger = scope.ServiceProvider.GetRequiredService<ILogger<Startup>>();
            var config = scope.ServiceProvider.GetRequiredService<IConfiguration>();
            var factory = scope.ServiceProvider.GetRequiredService<ISqlSugarClientFactory>();

            try
            {
                var dbName = config[DatabaseConstants.DefaultDatabase] ?? DatabaseConstants.MariaDB;
                var dbSettings = GetDatabaseSettings(config, dbName);

                using (var masterDb = CreateMasterConnection(config, dbName, dbSettings.DbType))
                {
                    if (dbSettings.DbType == DbType.PostgreSQL)
                    {
                        var dbExists = masterDb.Ado.GetInt($"SELECT 1 FROM pg_database WHERE datname = '{dbSettings.Database}'") > 0;
                        if (!dbExists)
                        {
                            Console.WriteLine($"Creating database {dbSettings.Database}...");
                            masterDb.Ado.ExecuteCommand($"CREATE DATABASE \"{dbSettings.Database}\"");
                            Console.WriteLine("Database created successfully");
                        }
                    }
                    else
                    {
                        if (!masterDb.DbMaintenance.GetDataBaseList().Contains(dbSettings.Database))
                        {
                            Console.WriteLine($"Creating database {dbSettings.Database}...");
                            masterDb.DbMaintenance.CreateDatabase(dbSettings.Database, null);
                            Console.WriteLine("Database created successfully");
                        }
                    }
                }

                using (var db = factory.CreateClient(dbName))
                {
                    Console.WriteLine("Initializing database schema...");

                    var entityBuilder = new EntityBuilder();

                    var dbTables = db.DbMaintenance.GetTableInfoList()
                                    .Select(a => a.Name.Trim().ToLower())
                                    .ToList();

                    var initializeTables = entityBuilder.EntityTypes
                                    .Select(a => a.Name.Trim().ToLower())
                                    .ToList();

                    var needCreateTable = initializeTables
                                    .Except(dbTables, StringComparer.OrdinalIgnoreCase)
                                    .ToList();

                    var needCreateTableTypes = needCreateTable
                       .Select(tableName => entityBuilder.EntityTypes.SingleOrDefault(type => type.Name.Trim().Equals(tableName, StringComparison.OrdinalIgnoreCase)))
                       .Where(type => type != null)
                       .ToArray();

                    db.CodeFirst.InitTables(needCreateTableTypes);

                    Console.WriteLine("Database schema initialized");

                    SeedInitialData(db);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Database initialization failed:{ex.Message}");
                throw;
            }
        }

        private (string Database, DbType DbType) GetDatabaseSettings(IConfiguration config, string dbName)
        {
            var dbType = SqlSugarClientFactory.GetDbType(dbName);
            var connectionString = GetConnectionString(config, dbName);

            switch (dbType)
            {
                case DbType.MySql:
                case DbType.MySqlConnector:
                    var builder = new MySqlConnectionStringBuilder(connectionString);
                    return (builder.Database, dbType);
                case DbType.SqlServer:
                    var sqlBuilder = new SqlConnectionStringBuilder(connectionString);
                    return (sqlBuilder.InitialCatalog, dbType);
                case DbType.Oracle:
                    var oracleBuilder = new OracleConnectionStringBuilder(connectionString);
                    return (oracleBuilder.DataSource, dbType);
                case DbType.PostgreSQL:
                    var pgsqlBuilder = new NpgsqlConnectionStringBuilder(connectionString);
                    return (pgsqlBuilder.Database, dbType);
                default:
                    throw new NotSupportedException($"Unsupported DbType: {dbType}");
            }
        }

        private SqlSugarClient CreateMasterConnection(IConfiguration config, string dbName, DbType dbType)
        {
            var connectionString = GetConnectionString(config, dbName);

            dynamic builder = null;

            switch (dbType)
            {
                case DbType.MySql:
                case DbType.MySqlConnector:
                    builder = new MySqlConnectionStringBuilder(connectionString)
                    {
                        Database = null,
                    };
                    break;
                case DbType.SqlServer:
                    builder = new SqlConnectionStringBuilder(connectionString)
                    {
                        InitialCatalog = "master",
                        ConnectTimeout = 30
                    };
                    break;
                case DbType.Oracle:
                    builder = new OracleConnectionStringBuilder(connectionString)
                    {
                        UserID = "sys as sysdba",
                    };
                    break;
                case DbType.PostgreSQL:
                    builder = new NpgsqlConnectionStringBuilder(connectionString)
                    {
                        Database = "postgres",
                        Timeout = 30,
                        Pooling = false
                    };
                    break;
            }

            return new SqlSugarClient(new ConnectionConfig
            {
                ConnectionString = builder?.ConnectionString ?? string.Empty,
                DbType = dbType,
                IsAutoCloseConnection = true,
                ConfigureExternalServices = new ConfigureExternalServices
                {
                    EntityService = (c, p) =>
                    {
                        if (dbType == DbType.PostgreSQL && p.IsPrimarykey && p.IsIdentity)
                            p.IsIdentity = true;
                        p.OracleSequenceName = $"seq_{c.Name.ToLower()}";
                        if (dbType == DbType.Oracle && p.IsPrimarykey && p.IsIdentity)
                            p.OracleSequenceName = "SEQ_" + c.Name;
                    }
                }
            });
        }

        private string GetConnectionString(IConfiguration config, string dbName)
        {
            if (Environment.GetEnvironmentVariable(DatabaseConstants.DockerEnv) != null)
            {
                return Environment.GetEnvironmentVariable($"{dbName}ConnectStr")
                    ?? throw new ArgumentException($"Environment variable {dbName}ConnectStr not found");
            }
            return config.GetConnectionString($"{dbName}ConnectStr");
        }

        private void SeedInitialData(ISqlSugarClient db)
        {
            Console.WriteLine("Initializing database data...");

            try
            {
                var entityBuilder = new EntityBuilder();
                var entitiesToAdd = new List<object>();

                var sortedEntities = entityBuilder.GetEntityDatas()
                    .OrderBy(entity => entity switch
                    {
                        AdministratorType _ => 1,
                        Department _ => 2,
                        Position _ => 3,
                        Nation _ => 4,
                        Education _ => 5,
                        PassportType _ => 6,
                        Menu _ => 7,
                        NavBar _ => 8,
                        SystemInformation _ => 9,
                        PromotionContent _ => 10,
                        Administrator _ => 11,
                        Employee _ => 12,
                        _ => 99
                    })
                    .ToList();

                Console.WriteLine($"Total entities to process: {sortedEntities.Count}");

                foreach (var entityData in sortedEntities)
                {
                    Console.WriteLine($"Processing entity: {entityData.GetType().Name}");

                    try
                    {
                        bool alreadyExists = false;

                        switch (entityData)
                        {
                            case Menu menu:
                                alreadyExists = db.Queryable<Menu>().Any(a => a.Key == menu.Key);
                                break;

                            case Administrator admin:
                                alreadyExists = db.Queryable<Administrator>().Any(a => a.Account == admin.Account);
                                break;

                            case AdministratorType adminType:
                                alreadyExists = db.Queryable<AdministratorType>().Any(a => a.TypeName == adminType.TypeName);
                                break;

                            case NavBar navBar:
                                alreadyExists = db.Queryable<NavBar>().Any(a => a.NavigationBarName == navBar.NavigationBarName);
                                break;

                            case PromotionContent promoContent:
                                alreadyExists = db.Queryable<PromotionContent>().Any(a => a.PromotionContentMessage == promoContent.PromotionContentMessage);
                                break;

                            case SystemInformation sysInfo:
                                alreadyExists = db.Queryable<SystemInformation>().Any(a => a.UrlAddress == sysInfo.UrlAddress);
                                break;

                            case Nation nation:
                                alreadyExists = db.Queryable<Nation>().Any(a => a.NationName == nation.NationName);
                                break;

                            case Department department:
                                alreadyExists = db.Queryable<Department>().Any(a => a.DepartmentNumber == department.DepartmentNumber);
                                break;

                            case Position position:
                                alreadyExists = db.Queryable<Position>().Any(a => a.PositionName == position.PositionName);
                                break;

                            case Education education:
                                alreadyExists = db.Queryable<Education>().Any(a => a.EducationName == education.EducationName);
                                break;

                            case PassportType passportType:
                                alreadyExists = db.Queryable<PassportType>().Any(a => a.PassportName == passportType.PassportName);
                                break;

                            case Employee employee:
                                alreadyExists = db.Queryable<Employee>().Any(a => a.EmployeeId == employee.EmployeeId);
                                break;
                        }

                        if (alreadyExists)
                        {
                            Console.WriteLine($"{entityData.GetType().Name} already exists, skipping.");
                            continue;
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"Error checking existence for {entityData.GetType().Name}: {ex.Message}");
                        throw;
                    }

                    entitiesToAdd.Add(entityData);
                }

                if (entitiesToAdd.Count > 0)
                {
                    Console.WriteLine($"Adding {entitiesToAdd.Count} initial data records");

                    var groupedEntities = entitiesToAdd
                        .GroupBy(e => e.GetType())
                        .ToList();

                    foreach (var group in groupedEntities)
                    {
                        Console.WriteLine($"Inserting {group.Count()} records of type {group.Key.Name}");

                        var list = group.ToList();

                        db.InsertableByObject(list).ExecuteCommand();
                    }

                    Console.WriteLine("Data inserted successfully");
                }
                else
                {
                    Console.WriteLine("No initial data to add");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Database data initialization failed: {ex.Message}");
                Console.WriteLine($"StackTrace: {ex.StackTrace}");

                if (ex.InnerException != null)
                {
                    Console.WriteLine($"Inner Exception: {ex.InnerException.Message}");
                    Console.WriteLine($"Inner StackTrace: {ex.InnerException.StackTrace}");
                }

                throw;
            }
            finally
            {
                Console.WriteLine("Database data initialization completed");
                Console.WriteLine($"administrator account：admin");
                Console.WriteLine($"administrator password：admin");
            }
        }
        #endregion
    }
}
